Use input selections to limit the query

This query in its current state will return all sales records for all employees.  However the query is to be limited by the input selections entered on the form.   Therefore, a WHERE clause is required to limit the query to selected employees within a date range.

Within the DataBlock Designer, double-click on the object that was just created which contains the results of the query.  Then click the hardhat/hammer (Edit Visual Design) icon to bring up the Build Query dialog box.  Click the “Conditional Fields (WHERE) tab where you can create the required WHERE clause.

The WHERE clause should only obtain records where:

The above limits the query to the selections entered by the person executing the report.  Each of the above conditions will be entered under the Conditional Fields tab.

This image shows the fields that can be entered under the WHERE tab.

The condition for 'Employees.last_name' will be created first. If you already have the Build Query open, then skip to Step 3.

  1. Double-click on the multi-column listbox, that we edited in the previous exercise, to launch the Choice Entry window.
  2. Click Edit Visual Design (the edit visual design button) to launch the Build Query window.
  3. Select the Conditional Fields (WHERE) tab.
  4. Double-click the 'last_name' field in the 'Employees' table to auto-populate the first three fields.
    This shows the employee last name entered under the WHERE tab.
  5. Click inside the Condition field, then click the ellipsis button ellipses button that appears inside the field.
  6. Insert an Equals sign (=).
  7. Click the insert variable icon insert variable icon to display the variables that exist within the DataBlock. 
  8. Select 'last_name'.  Recall that EmployeeList is the name of the multi-column list box containing the list of employee names.  The two fields within the Employee table are 'last_name' and 'first_name'. 
    The Pick a Variable dialog box where the last name is selected to be used as the condition.
  9. Click OK to continue.
    The SQL Editor with the employee last name entered.
  10. Click OK to continue.
    This image shows all of the fields entered under the WHERE tab. It includes the employee last name from the database and the and the employee last name entered as a search parameter on the DataBlock form.
    The condition for 'last_name' has been created, as shown in the above figure.
  11. Click on the blank field to the right of the field just created and repeat Steps 4 through 10 for the employee first name.
    This image shows the employee last name added as a condition under the WHERE tab.

After adding the employee first and last names, we will need to add date conditions using 'sale_date' from the 'Orders' table, and the 'StartDate' and 'EndDate' variables (which were added to the dashboard in a previous exercise).

  1. Double-click 'sale_date' in the 'Orders' table to auto-populate the and/or, Table, and Field fields.
  2. Click inside the Condition field, then click the ellipses button ellipses button that appears inside the field to launch the SQL Editor.
  3. Enter Greater than or equal to (>=).
  4. Click the insert variable icon insert variable icon to see the list of available variables. 
  5. Select StartDate from the variable list.
  6. Repeat from Step 12 to add the condition Less than or equal to the End Date (<= :EndDate) to 'sale_date'.
    This shows the start date and end date added as conditions under the WHERE tab.
  7. Click OK and validate the query.
  8. Click Finish to complete the design of the WHERE clause and return to the DataBlock Designer.
  9. Click Save to save your work, then click the Test button test report button to test the dashboard.
    Testing the dashboard we just created. The listbox with our new query is showing sales results for the selected employee between June of 2008 and June of 2010.